package DataBase;

import InterFace.SQL_Readin_api;
import pojo.*;

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.jar.JarOutputStream;

public class SQL_Readin implements SQL_Readin_api {
    private String url = "jdbc:mysql://localhost:3306/class_manage";
    static Connection connection;
    public SQL_Readin(){
    }
    public Connection connect() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url,"root","1234");
        return connection;
    }

    public String[] readClassID(){//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            System.out.println(e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
            System.out.println(e.getMessage());
        }
        String sql = "select * from classid";
        try {
            Statement Statement = connection.createStatement();
            ResultSet rs = Statement.executeQuery(sql);
            ArrayList<String> list = new ArrayList<>();
            while(rs.next()){
                System.out.println(rs.getString(1));
                list .add( rs.getString(1));
            }
            String[] arr = new String[list.size()];
            arr = list.toArray(arr);
            return arr;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public String[] readUnitPosition(){//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
            System.out.println(e.getMessage());
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
            System.out.println(e.getMessage());
        }
        String sql = "select * from unit_position";
        try {
            Statement Statement = connection.createStatement();
            ResultSet rs = Statement.executeQuery(sql);
            ArrayList<String> list = new ArrayList<>();
            while(rs.next()){
                System.out.println(rs.getString(1));
                list .add( rs.getString(1));
            }
            String[] arr = new String[list.size()];
            arr = list.toArray(arr);
            return arr;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Boolean userSign(String number, String password,String classId, String table){//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement = null;
        String sql = "";
        if (table.equals("user_man")){
            sql="insert into user_man values(?,?,?)";
        }
        else if (table.equals("user_nor")){
            sql="insert into user_nor values(?,?,?)";
        }
        try {
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setString(1,number);
            preparedStatement.setString(2,password);
            preparedStatement.setString(3,classId);
            if(preparedStatement.executeUpdate()>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }

    public String userLog(String number, String password, String table) throws SQLException {//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql = "";
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        System.out.println(password);
        if (table.equals("user_man")) {
            sql = "select * from user_man where number = ? and password = ? ";
        }
        else if (table.equals("user_nor")) {
            sql = "select * from user_nor where number = ? and password = ? ";
        }
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,number);
        preparedStatement.setString(2,password);
        resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            return resultSet.getString(3);
        }
        connection.close();
        return "**/**";
    }
    public List<ClassMember> readPeople(String className) throws SQLException {//姚
        List<ClassMember> members  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from classmember where class_name = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            ClassMember classMember = new ClassMember();
            classMember.setStudentId(resultSet.getInt("student_id"));
            classMember.setNumber(resultSet.getString("number"));
            classMember.setName(resultSet.getString("name"));
            classMember.setSex(resultSet.getString("sex"));
            classMember.setAge(resultSet.getInt("sage"));
            classMember.setContactInfo(resultSet.getString("contact_info"));
            classMember.setDormitoryAddress(resultSet.getString("dormitory_address"));
            classMember.setClassId(resultSet.getString(8));
            members.add(classMember);
        }
        connection.close();
        return members;
    }
    public boolean deleteClassMember(String number) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "delete from classmember where number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,number);
        preparedStatement.execute("SET FOREIGN_KEY_CHECKS=0");
        if(preparedStatement.executeUpdate() > 0){
            preparedStatement.execute("SET FOREIGN_KEY_CHECKS=1");
            connection.close();
            return true;
        }
        connection.close();
        return false;
    }
    public boolean addClassMember(ClassMember classMember) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement ps;
        ps = connection.prepareStatement("select * from classmember where number = ?");
        ps.setString(1,classMember.getNumber());
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            JOptionPane.showMessageDialog(null,"该学号已存在。");
        }
        else {
            PreparedStatement preparedStatement;
            String sql = "insert into classmember values(?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, classMember.getStudentId());
            preparedStatement.setString(2, classMember.getNumber());
            preparedStatement.setString(3, classMember.getName());
            preparedStatement.setString(4, classMember.getSex());
            preparedStatement.setInt(5, classMember.getAge());
            preparedStatement.setString(6, classMember.getContactInfo());
            preparedStatement.setString(7, classMember.getDormitoryAddress());
            preparedStatement.setString(8, classMember.getClassId());
            if (preparedStatement.executeUpdate() > 0) {
                connection.close();
                return true;
            }
        }
        connection.close();
        return false;
    }
    public boolean updateClassMember(ClassMember classMember) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql ="update classmember set  name = ? , sex = ? , sage = ? , contact_info = ? , dormitory_address = ? ,class_name = ? where number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,classMember.getName());
        preparedStatement.setString(2,classMember.getSex());
        preparedStatement.setInt(3,classMember.getAge());
        preparedStatement.setString(4,classMember.getContactInfo());
        preparedStatement.setString(5,classMember.getDormitoryAddress());
        preparedStatement.setString(7,classMember.getNumber());
        preparedStatement.setString(6,classMember.getClassId());
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        connection.close();
        return false;
    }
    public ArrayList<String> nor_getAllClassMember() throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql = "select * from messageboard ";
        ResultSet resultSet;
        ArrayList<String> list = new ArrayList<>();
        resultSet = connection.createStatement().executeQuery(sql);
        while(resultSet.next()){
            list.add(resultSet.getString(3)+" , "+resultSet.getString(4)+" , "+resultSet.getString(5));
        }
        return list;
    }
    public ArrayList<String> nor_getMoney() throws SQLException {
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql = "select * from expenserecord ";
        ResultSet resultSet;
        ArrayList<String> list = new ArrayList<>();
        resultSet = connection.createStatement().executeQuery(sql);
        while (resultSet.next()) {
            list.add(resultSet.getString(2) + " , " + resultSet.getString(3) + " , " + resultSet.getString(4) + " , " + resultSet.getString(5));
        }
        return list;
    }
    public ArrayList<String> nor_getAction() throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql = "select * from activityrecord";
        ResultSet resultSet;
        ArrayList<String> list = new ArrayList<>();
        resultSet = connection.createStatement().executeQuery(sql);
        while (resultSet.next()) {
            list.add(resultSet.getString(2) + " , " + resultSet.getString(3) + " , " + resultSet.getString(4) + " , " + resultSet.getString(5)+ " , " + resultSet.getString(6));
        }
        return list;
    }
    public ArrayList<String> nor_getRePun() throws SQLException {
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql = "select * from rewardpunishmentrecord";
        ResultSet resultSet;
        ArrayList<String> list = new ArrayList<>();
        resultSet = connection.createStatement().executeQuery(sql);
        while (resultSet.next()) {
            list.add(resultSet.getString(2) + " , " + resultSet.getString(3) + " , " + resultSet.getString(4) + " , " + resultSet.getString(5));
        }
        return list;


    }
    public boolean nor_add_message(ArrayList<String> list) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        String sql="insert into messageboard values(?,?,?,?,?,?)";
        PreparedStatement preparedStatement;
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,list.get(0));
        preparedStatement.setString(2,list.get(1));
        preparedStatement.setString(3,list.get(2));
        preparedStatement.setString(4,list.get(3));
        preparedStatement.setString(5,list.get(4));
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        return false;
    }

    public List<CommitteeMember> readUnit(String className) throws SQLException {//姚
        List<CommitteeMember> units  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from committeemember where ClassName = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            CommitteeMember committeeMember = new CommitteeMember();
            committeeMember.setNumber(resultSet.getString(3));
            committeeMember.setAppointmentId(resultSet.getInt(1));
            committeeMember.setStudentId(resultSet.getInt(2));
            committeeMember.setStuName(resultSet.getString(4));
            committeeMember.setPosition(resultSet.getString(5));
            committeeMember.setAppointmentDate(resultSet.getDate(6));
            committeeMember.setTermDuration(resultSet.getString(7));
            committeeMember.setClassName(resultSet.getString(8));
            units.add(committeeMember);
        }
        connection.close();
        return units;
    }
    public Boolean addConmmitteeMemeber(CommitteeMember committeeMember) throws SQLException {//姚
        //根据number去classmember中查学生并添加到committeemember
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement, preparedStatement1;
        ResultSet resultSet;
        String sql = "select * from classmember where number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,committeeMember.getNumber());
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            Integer studentId = resultSet.getInt("student_id");
            String sql1 = "insert into committeemember (student_id, number,stu_name, position, appointment_date,term_duration,ClassName)  values (?,?,?,?,?,?,?)";
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setInt(1,studentId);
            preparedStatement1.setString(2,committeeMember.getNumber());
            preparedStatement1.setString(3,committeeMember.getStuName());
            preparedStatement1.setString(4,committeeMember.getPosition());
            preparedStatement1.setDate(5,committeeMember.getAppointmentDate());
            preparedStatement1.setString(6,committeeMember.getTermDuration());
            preparedStatement1.setString(7,committeeMember.getClassName());
            if(preparedStatement1.executeUpdate() > 0){
                //System.out.println("添加成功");
                connection.close();
                return true;
            }
        }
        return false;
    }
    public Boolean deleteCommitteeStuById(String number) throws SQLException {//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "DELETE FROM committeemember WHERE number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,number);
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        return false;
    }

    public List<DutySchedule> readDuty(String className) throws SQLException {//郭
        List<DutySchedule> duty  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from dutyschedule where ClassName = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            DutySchedule dutySchedule = new DutySchedule();
            dutySchedule.setNumber(resultSet.getString(3));
            dutySchedule.setStudentId(resultSet.getInt(2));
            dutySchedule.setStuName(resultSet.getString(4));
            dutySchedule.setDutyId(resultSet.getInt(1));
            dutySchedule.setTaskDescription(resultSet.getString(6));
            dutySchedule.setDutyDate(resultSet.getDate(5));
            dutySchedule.setClassName(resultSet.getString(7));
            duty.add(dutySchedule);
        }
        connection.close();
        return duty;
    }
    public boolean deleteDutyById(String number) throws SQLException {//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "DELETE FROM dutyschedule WHERE number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,number);
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        return false;
    }
    public boolean addDutyStu (DutySchedule dutySchedule) throws SQLException {//姚
        //根据number去classmember中查学生并添加到dutyschdule
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement, preparedStatement1;
        ResultSet resultSet;
        String sql = "select * from classmember where number = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,dutySchedule.getNumber());
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            Integer studentId = resultSet.getInt("student_id");

            String sql1 = "insert into dutyschedule (student_id, name, number, duty_date,task_description,ClassName) values (?,?,?,?,?,?)";
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement1.setInt(1,studentId);
            preparedStatement1.setString(2,dutySchedule.getStuName());
            preparedStatement1.setString(3,dutySchedule.getNumber());
            preparedStatement1.setDate(4,dutySchedule.getDutyDate());
            preparedStatement1.setString(5,dutySchedule.getTaskDescription());
            preparedStatement1.setString(6,dutySchedule.getClassName());
            if(preparedStatement1.executeUpdate() > 0){
                connection.close();
                return true;
            }
        }
        return false;
    }
    public List<ExpenseRecord> readMoney(String className) throws SQLException , ClassNotFoundException {//姚
        List<ExpenseRecord> moneys  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from expenserecord where ClassName = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            ExpenseRecord expenseRecord = new ExpenseRecord();
            expenseRecord.setRecordId(resultSet.getInt(1));
            expenseRecord.setExpenseType(resultSet.getString(2));
            expenseRecord.setAmount(resultSet.getBigDecimal(3));
            expenseRecord.setExpenseDate(resultSet.getDate(4));
            expenseRecord.setRemarks(resultSet.getString(5));
            expenseRecord.setClassName(className);
            moneys.add(expenseRecord);
        }
        connection.close();
        return moneys;
    }

    public boolean addExpenseMoney(ExpenseRecord expenseRecord) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "insert into  expenserecord (expense_type, amount, expense_date, remarks, ClassName) values(?,?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,expenseRecord.getExpenseType());
        preparedStatement.setBigDecimal(2,expenseRecord.getAmount());
        preparedStatement.setDate(3,expenseRecord.getExpenseDate());
        preparedStatement.setString(4,expenseRecord.getRemarks());
        preparedStatement.setString(5,expenseRecord.getClassName());
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        connection.close();
        return false;
    }

    public boolean deleteMoneyExpense(String s) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "delete from expenserecord where record_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,s);
        if(preparedStatement.executeUpdate() > 0){
            return true;
        }
        return false;
    }
    public List<MessageBoard> readMessage(String className) throws SQLException {//郭
        List<MessageBoard> message  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        } catch (ClassNotFoundException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from messageboard ";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            MessageBoard messageBoard = new MessageBoard();
            messageBoard.setMessageId(resultSet.getInt(1));
            messageBoard.setStudentId(resultSet.getInt(2));
            messageBoard.setStudentName(resultSet.getString(3));
            messageBoard.setMessageContent(resultSet.getString(4));
            messageBoard.setMessageDate(resultSet.getString(5));
            messageBoard.setClassName(resultSet.getString(6));
            message.add(messageBoard);
        }
        connection.close();
        return message;
    }

    public boolean addMessageBoard(MessageBoard messageBoard) throws SQLException {//王
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        } catch (ClassNotFoundException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        }
        PreparedStatement preparedStatement;
        String sql = "insert into messageboard (stu_name,message_content,message_date,ClassName) values(?,?,?,?)";
        preparedStatement=connection.prepareStatement(sql);
        preparedStatement.setString(1,messageBoard.getStudentName());
        preparedStatement.setString(2,messageBoard.getMessageContent());
        preparedStatement.setString(3,messageBoard.getMessageDate());
        preparedStatement.setString(4,messageBoard.getClassName());
        System.out.println(messageBoard.getClassName());
        if(preparedStatement.executeUpdate() > 0){
            connection.close();
            return true;
        }
        connection.close();
        return false;
    }

    public boolean deleteMessageBoard(String s) throws SQLException {//王
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        } catch (ClassNotFoundException e) {
            System.out.println("鏁版嵁搴撹繛鎺ュけ璐�");
        }
        PreparedStatement preparedStatement;
        String sql = "delete from messageboard where message_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,s);
        if(preparedStatement.executeUpdate() > 0){
            return true;
        }
        return false;
    }
    public List<ActivityRecord> getAllActivity(String className) throws SQLException {//姚
        List<ActivityRecord> list = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = " select * from activityrecord where ClassName = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            ActivityRecord activityRecord = new ActivityRecord();
            activityRecord.setActivityId(resultSet.getInt("activity_id"));
            activityRecord.setHost(resultSet.getString("host"));
            activityRecord.setActivityDate(resultSet.getDate("activity_date"));
            activityRecord.setActivityContent(resultSet.getString("activity_content"));
            activityRecord.setActivityName(resultSet.getString("activity_name"));
            activityRecord.setLocation(resultSet.getString("location"));
            activityRecord.setParticipantsCount(resultSet.getInt("participants_count"));
            activityRecord.setClassName(className);
            list.add(activityRecord);
        }
        if (!list.isEmpty()) {
            connection.close();
            return list;
        } else {
            System.out.println("没有活动或者未读取到数据");
        }
        return null;
    }
    public boolean deleteActivityById(Integer id) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "delete from activityrecord where activity_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        if (preparedStatement.executeUpdate() > 0) {
            connection.close();
            return true;
        } else {
            connection.close();

        }
        return false;
    }
    public boolean addActivity(ActivityRecord activityRecord) throws SQLException {//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "INSERT INTO activityrecord (host,activity_name, activity_date, location, participants_count, activity_content,ClassName) VALUES (?,?,?,?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,activityRecord.getHost());
        preparedStatement.setString(2,activityRecord.getActivityName());
        preparedStatement.setDate(3,activityRecord.getActivityDate());
        preparedStatement.setString(4,activityRecord.getLocation());
        preparedStatement.setInt(5,activityRecord.getParticipantsCount());
        preparedStatement.setString(6,activityRecord.getActivityContent());
        preparedStatement.setString(7,activityRecord.getClassName());
        if (preparedStatement.executeUpdate() > 0) {
            connection.close();
            return true;
        } else {
            connection.close();
        }
        return false;
    }
    public boolean deleteRePun(int s) throws SQLException {//郭
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "delete from rewardpunishmentrecord where record_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,s);
        if(preparedStatement.executeUpdate() > 0){
            return true;
        }
        return false;
    }

    public List<RewardPunishmentRecord> readRePun(String className) throws SQLException {//姚
        List<RewardPunishmentRecord> RePun  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from  rewardpunishmentrecord where ClassName = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,className);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            RewardPunishmentRecord rePunRecord = new RewardPunishmentRecord();
            //获得了奖惩记录中的student_id,根据student_id去classnumber中查找学生的name，number然后储存到rePunRecord中并将rePunRecord添加到RePun中，最后返回
            PreparedStatement ps;
            ResultSet rs;
            String sql1 = "select * from classmember where student_id = ?";
            ps = connection.prepareStatement(sql1);
            ps.setInt(1,resultSet.getInt("student_id"));
            rs = ps.executeQuery();
            if(rs.next()){
                rePunRecord.setRecordId(resultSet.getInt("record_id"));
                rePunRecord.setName(rs.getString("name"));
                rePunRecord.setNumber(rs.getString("number"));
                rePunRecord.setReason(resultSet.getString("reason"));
                rePunRecord.setRpType(resultSet.getString("rp_type"));
                rePunRecord.setRpDate(resultSet.getDate("rp_date"));
                RePun.add(rePunRecord);
            }
        }
        if(!RePun.isEmpty()){
            //System.out.println("数据读取失败");
            connection.close();
        }else {
            //System.out.println("读取成功");
            connection.close();
        }

        return RePun;
    }
    public List<RewardPunishmentRecord> readRePun_findNum(String num) throws SQLException {//郭
        List<RewardPunishmentRecord> RePun  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        PreparedStatement preparedStatement1;
        ResultSet resultSet;
        String sql = "select student_id from  classmember where  number= ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,num);
        resultSet = preparedStatement.executeQuery();
        resultSet.next();
        String id = resultSet.getString("student_id");
        String sql2 = "select * from  rewardpunishmentrecord where student_id = ?";
        preparedStatement1 = connection.prepareStatement(sql2);
        preparedStatement1.setString(1,id);
        resultSet = preparedStatement1.executeQuery();
        while (resultSet.next()) {
            RewardPunishmentRecord rePunRecord = new RewardPunishmentRecord();
            //获得了奖惩记录中的student_id,根据student_id去classnumber中查找学生的name，number然后储存到rePunRecord中并将rePunRecord添加到RePun中，最后返回
            PreparedStatement ps;
            ResultSet rs;
            String sql1 = "select * from classmember where student_id = ?";
            ps = connection.prepareStatement(sql1);
            ps.setInt(1,resultSet.getInt("student_id"));
            rs = ps.executeQuery();
            if(rs.next()){
                rePunRecord.setRecordId(resultSet.getInt("record_id"));
                rePunRecord.setName(rs.getString("name"));
                rePunRecord.setNumber(rs.getString("number"));
                rePunRecord.setReason(resultSet.getString("reason"));
                rePunRecord.setRpType(resultSet.getString("rp_type"));
                rePunRecord.setRpDate(resultSet.getDate("rp_date"));
                RePun.add(rePunRecord);
            }
        }
        if(!RePun.isEmpty()){
            //System.out.println("数据读取失败");
            connection.close();
        }else {
            //System.out.println("读取成功");
            connection.close();
        }

        return RePun;
    }
    /*public List<RewardPunishmentRecord> readRePun() throws SQLException {
        List<RewardPunishmentRecord> RePun  = new ArrayList<>();
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        ResultSet resultSet;
        String sql = "select * from  rewardpunishmentrecord ";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        String sql1="select name,number from classmember where student_id = ?";
        PreparedStatement p1 = connection.prepareStatement(sql1);
        ResultSet rs1;
        while (resultSet.next()) {
            RewardPunishmentRecord rePunRecord = new RewardPunishmentRecord();
            rePunRecord.setRecordId(resultSet.getInt(1));
            rePunRecord.setStudentId(resultSet.getInt(2));
            p1.setInt(1,rePunRecord.getStudentId());
            rs1 = p1.executeQuery();
            rs1.next();
            System.out.println(rs1.getString(1));
            rePunRecord.setName(rs1.getString(1));
            rePunRecord.setNumber(rs1.getString(2));
            rs1.previous();
            System.out.println(rePunRecord.getName());
            System.out.println(rePunRecord.getNumber());
            rePunRecord.setRpType(resultSet.getString(3));
            rePunRecord.setReason(resultSet.getString(4));
            rePunRecord.setRpDate(resultSet.getDate(5));
            RePun.add(rePunRecord);
        }
        connection.close();
        return RePun;
    }*/
    public boolean addRewardPun(RewardPunishmentRecord rePunRecord){//姚
        try {
            this.connection = this.connect();
        } catch (SQLException e) {
            System.out.println("数据库连接失败");
        } catch (ClassNotFoundException e) {
            System.out.println("数据库连接失败");
        }
        PreparedStatement preparedStatement;
        String sql = "INSERT INTO rewardpunishmentrecord (student_id, rp_type, reason, rp_date,ClassName) values (?,?,?,?,?)";
        int id = -1;
        String sql2="select * from classmember where number = ?";
        PreparedStatement preparedStatement2;
        try {
            preparedStatement2 = connection.prepareStatement(sql2);
            preparedStatement2.setString(1,rePunRecord.getNumber());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            ResultSet resultSet = preparedStatement2.executeQuery();
            if (resultSet.next()) {
                id=resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try{
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.setString(2,rePunRecord.getRpType());
            preparedStatement.setString(3,rePunRecord.getReason());
            preparedStatement.setDate(4,rePunRecord.getRpDate());
            preparedStatement.setString(5,rePunRecord.getClassName());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            if (preparedStatement.executeUpdate() > 0) {
                connection.close();
                return true;
            } else {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return false;
    }
}



